import openpyxl
from openpyxl.utils import get_column_letter, column_index_from_string
from openpyxl.styles import Font, colors

# 创建Excel文件，加载工作簿
workbook = openpyxl.Workbook()

worksheet = workbook.active
print(worksheet.title)
worksheet.title = 'demoSheet'
print(worksheet.title)

workbook.create_sheet(index=1, title='createdSheet1')
workbook.create_sheet(index=2, title='createdSheet2')
workbook.create_sheet(index=3, title='createdSheet2')

print(workbook.sheetnames)

# 使用list写入数据
rows = [['a', 'b', 'c'], [1, 2, 3], [11, 22, 33]]
for row in rows:
    worksheet.append(row)

# 使用range写入数据   range范围左闭右开
for index in range(1, 20):
    worksheet.append(range(1, 16))

for row in range(1, 21):
    for column in range(1, 16):
        worksheet.cell(row, column, value=get_column_letter(column))

# 修改字体
font = Font(name='华文楷体', color=colors.COLOR_INDEX[2], size=30, italic=True)
worksheet['C3'].font = font

# 设置计算公式
worksheet['D1'] = 100
worksheet['D2'] = 200
worksheet['D3'] = '=SUM(D1:D2)'

# 设置行高和列宽
worksheet.row_dimensions[2].height = 100
worksheet.column_dimensions['B'].width = 50

# 合并单元格
worksheet.merge_cells('C5:E7')

# 拆分单元格 拆分的必须要是原先合并的，不能部分拆分
worksheet.unmerge_cells('C5:E7')

workbook.save('demo02.xlsx')
